package android.dataaccess;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.MalformedURLException;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;
import util.Constants;
import util.LollaDbUtil;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.Bitmap;
import android.graphics.Bitmap.CompressFormat;
import android.graphics.BitmapFactory;
import android.util.Log;
import android.util.Pair;
import controller.LollaWrapper;
import entities.Band;
import entities.Event;
import entities.LollaObject;
import entities.Update;
import entities.Venue;

public class LollaDbAdapter {

	//db name
	private static final String dbName = "lollaDb";

	//band table members
	private static final String bandTable = "Bands";
	private static final String bandID = "BandID";
	private static final String bandName = "BandName";
	private static final String bandFlickrTag = "FlickrTag";
	private static final String bandPhoto = "Photo";
	private static final String bandPhotoThumb = "PhotoThumb";
	private static final String bandItunesLink = "ItunesLink";
	private static final String bandDisplayDate = "DisplayDate";
	private static final String bandMainLineupPosition = "MainLineupPosition";
	private static final String bandFanCount = "FanCount";
	private static final String bandSetlist = "SetList";
	private static final String bandMyspaceLink = "MyspaceLink";
	private static final String bandHometown = "Hometown";
	private static final String bandHomepage = "Homepage";
	private static final String bandYoutubeID = "YoutubeID";
	private static final String bandTwitterUsername = "TwitterUsername";
	private static final String bandEchonestID = "EchonestID";
	private static final String bandMusicbrainzID = "MusicBrainzID";
	private static final String bandDescription = "Description";
	private static final String bandAltDescription = "AltDescription";
	private static final String bandUpdatedAt = "UpdatedAt";
	private static final String bandAPILink = "BandAPILink";

	//event table members
	private static final String eventTable = "Events";
	private static final String eventID = "EventID";
	private static final String eventName = "EventName";
	private static final String eventPhotoThumb = "PhotoThumb";
	private static final String eventVotes = "Votes";
	private static final String eventApiLink = "ApiLink";
	private static final String eventEndTime = "EndTime";
	private static final String eventGoogleDate = "GoogleDate";
	private static final String eventUpdatedAt = "UpdatedAt";
	private static final String eventConference_category = "ConferenceCategory";
	private static final String eventTime = "Time";
	private static final String eventDate = "Date";
	private static final String eventCategory = "Category";
	private static final String eventPhotoThumbSmall = "PhotoThumnSmall";
	private static final String eventStartTime = "StartTime";

	//venues table members
	private static final String venuesTable = "Venues";
	private static final String venuesID = "VenuesID";
	private static final String venuesName = "VenuesName";
	private static final String venuesSortOrder = "SortOrder";
	private static final String venuesDescription = "Description";
	private static final String venuesUpdatedAt = "UpdatedAt";
	
	//image table members
	private static final String image = "Image";
	private static final String imageThumb = "ImageThumb";

	//many-to-many table
	private static final String event_band_table = "Event_Band";

	//user tables
	private static final String myBandsTable = "MyBands";
	
	//image tables
	private static final String bandImagesTable = "BandImages";

	private static final String TAG = "LollaDbAdapter";
	private DatabaseHelper lollaDbHelper;
	private static final int dbVersion = 2;
	private SQLiteDatabase lollaDb;

	private final Context mCtx;

	private static final String database_create_bands =
			// create band table
			new StringBuilder().append("create table ").append(bandTable).append("(")
					.append(bandID).append(" integer primary key, ").append(bandName)
					.append(" text, ").append(bandFlickrTag).append(" text, ")
					.append(bandPhoto).append(" text, ").append(bandPhotoThumb)
					.append(" text, ").append(bandItunesLink).append(" text, ")
					.append(bandDisplayDate).append(" text, ")
					.append(bandMainLineupPosition).append(" integer, ")
					.append(bandFanCount).append(" integer, ").append(bandSetlist)
					.append(" text, ").append(bandMyspaceLink).append(" text, ")
					.append(bandHometown).append(" text, ").append(bandHomepage)
					.append(" text, ").append(bandYoutubeID).append(" integer, ")
					.append(bandTwitterUsername).append(" text, ")
					.append(bandEchonestID).append(" integer, ")
					.append(bandMusicbrainzID)
					.append(" integer, ")
					.append(bandDescription)
					.append(" blob, ")
					.append(bandAltDescription)
					.append(" blob, ")
					.append(bandAPILink)
					.append(" text, ")
					.append(bandUpdatedAt)
					.append(" text); ").toString();

	private static final String database_create_events =
			// create event table
			new StringBuilder().append("create table ").append(eventTable).append("(")
					.append(eventID).append(" integer primary key, ").append(eventName)
					.append(" text, ").append(eventPhotoThumb).append(" text, ")
					.append(eventVotes).append(" integer, ").append(eventApiLink)
					.append(" text, ").append(eventEndTime).append(" text, ")
					.append(eventGoogleDate).append(" text, ").append(eventUpdatedAt)
					.append(" text, ").append(eventConference_category).append(" text, ")
					.append(eventDate).append(" text, ").append(eventCategory)
					.append(" text, ").append(eventTime).append(" text, ")
					.append(eventPhotoThumbSmall).append(" text, ").append(venuesID).append(" integer, ")
					.append(eventStartTime).append(" text);").toString();

	private static final String database_create_venues =
			// create venue table
			new StringBuilder().append("create table ").append(venuesTable).append("(").append(venuesID).append(" integer primary key, ")
					.append(venuesName).append(" text, ").append(venuesSortOrder).append(" text, ")
					.append(venuesDescription).append(" blob, ").append(venuesUpdatedAt).append(" text);")
					.toString();

	private static final String database_create_event_band =
			// create many-to-many table for events and bands
			new StringBuilder().append("create table ").append(event_band_table).append(" (ID INTEGER PRIMARY KEY AUTOINCREMENT, ").append(eventID).append(" integer, ")
					.append(bandID).append(" integer);").toString();

	private static final String database_create_my_bands =
			// create my bands table
			new StringBuilder().append("create table ").append(myBandsTable).append(" (ID INTEGER PRIMARY KEY AUTOINCREMENT, ")
					.append(bandID).append(" integer);").toString();
	
	private static final String database_create_band_images =
		   new StringBuilder().append("create table ").append(bandImagesTable).append("(")
		   .append(bandID).append(" integer primary key, ").append(image).append(" BLOB, ").append(imageThumb).append(" BLOB);").toString();

	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, dbName, null, dbVersion);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			Log.i(TAG, "Executing table create scripts");
			db.execSQL(database_create_bands);
			db.execSQL(database_create_events);
			db.execSQL(database_create_venues);
			db.execSQL(database_create_event_band);
			db.execSQL(database_create_my_bands);
			db.execSQL(database_create_band_images);
			Log.i(TAG, "Table create scripts executed");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS " + bandTable);
			db.execSQL("DROP TABLE IF EXISTS " + eventTable);
			db.execSQL("DROP TABLE IF EXISTS " + venuesTable);
			db.execSQL("DROP TABLE IF EXISTS " + event_band_table);
			db.execSQL("DROP TABLE IF EXISTS " + myBandsTable);
			db.execSQL("DROP TABLE IF EXISTS " + bandImagesTable);
			onCreate(db);
		}
	}

	public LollaDbAdapter(Context ctx) {
		this.mCtx = ctx;
	}

	public LollaDbAdapter open() throws SQLException {
		lollaDbHelper = new DatabaseHelper(mCtx);
		lollaDb = lollaDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		lollaDbHelper.close();
	}

	public long save(LollaObject object) {
		if (object.getClass() == Event.class) {
			return saveEvent((Event) object);
		} else if (object.getClass() == Band.class) {
			return saveBand((Band) object);
		} else if (object.getClass() == Venue.class) {
			return saveVenue((Venue) object);
		} else {
			return -1;
		}
	}
	
	public long saveOrUpdate(LollaObject object) {
		long update = update(object);
		if (update == 0)
			return save(object);
		else
			return update;
	}
	
	public void saveOrUpdateAll(List<LollaObject> objects) {
		Log.i(TAG, "Persisting or updating " + objects.size() + " items");
		for (LollaObject object : objects)
			saveOrUpdate(object);
		Log.i(TAG, "Items persisted or updated successfully");
		
	}

	public void saveAll(List<LollaObject> objects) {
		Log.i(TAG, "Persisting " + objects.size() + " items");
		for (LollaObject object : objects)
			save(object);
		Log.i(TAG, "Items persisted successfully");
	}

	public long addBandToMySchedule(Band band) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(bandID, band.getId());
		Log.i(TAG, "Inserting Band " + band.getTitle() + " into MyBands");
		return lollaDb.insert(myBandsTable, null, initialValues);
	}

	public void removeBandFromMySchedule(Band band) {
		Log.i(TAG, "Removing Band " + band.getTitle() + " from MyBands");
		lollaDb.delete(myBandsTable, bandID + " = " + band.getId(), null);
	}

	public void insertRelationships(List<Event> events) {
		ContentValues initialValues = new ContentValues();
		for (Event event : events) {
			for (Long band : event.getBandIds()) {
				initialValues.put(eventID, event.getId());
				initialValues.put(bandID, band);
				lollaDb.insert(event_band_table, null, initialValues);
			}
		}
	}

	private long saveEvent(Event event) {
		ContentValues initialValues = new ContentValues();
		populateContentValues(initialValues, event);
		Log.i(TAG, "Inserting Event " + event.getTitle());
		return lollaDb.insert(eventTable, null, initialValues);
	}

	private long saveBand(Band band) {
		ContentValues initialValues = new ContentValues();
		populateContentValues(initialValues, band);
		Log.i(TAG, "Inserting Band " + band.getTitle());
		return lollaDb.insert(bandTable, null, initialValues);
	}

	private long saveVenue(Venue venue) {
		ContentValues initialValues = new ContentValues();
		populateContentValues(initialValues, venue);
		Log.i(TAG, "Inserting Venue " + venue.getTitle());
		return lollaDb.insert(venuesTable, null, initialValues);
	}
	
	private long update(LollaObject entity) {
		ContentValues args = new ContentValues();
		populateContentValues(args, entity);
		if (entity.getClass() == Band.class) {
			return lollaDb.update(bandTable, args, bandID + "=" + entity.getId(), null);
		} else if (entity.getClass() == Event.class) {
			return lollaDb.update(eventTable, args, eventID + "=" + entity.getId(), null);
		} else if (entity.getClass() == Venue.class) {
			return lollaDb.update(venuesTable, args, venuesID + "=" + entity.getId(), null);
		} else {
			return 0;
		}
	}

	public void processMultipleUpdate(List<Update> updates) {
		for (Update update : updates) {
			processUpdate(update);
		}
	}

	public boolean processUpdate(Update update) {
		LollaWrapper lW = new LollaWrapper(Constants.API_KEY);
		String updateType = update.getUpdateType();
		if (updateType.equals("Event")) {
			Event event = null;
			try {
				event = lW.makeCall(update.getApiLink(), Event.class);
			} catch (Exception e) {
				e.printStackTrace();
			}
			ContentValues args = new ContentValues();
			populateContentValues(args, event);
			if (event != null) {
				return lollaDb.update(eventTable, args, eventID + "=" + event.getId(), null) > 0;
			}
		} else if (updateType.equals("Band")) {
			Band band = null;
			try {
				band = lW.makeCall(update.getApiLink(), Band.class);
			} catch (Exception e) {
				e.printStackTrace();
			}
			ContentValues args = new ContentValues();
			populateContentValues(args, band);
			if (band != null) {
				return lollaDb.update(bandTable, args, bandID + "=" + band.getId(), null) > 0;
			}
		} else if (updateType.equals("Venue")) {
			Venue venue = null;
			try {
				venue = lW.makeCall(update.getApiLink(), Venue.class);
			} catch (Exception e) {
				e.printStackTrace();
			}
			ContentValues args = new ContentValues();
			populateContentValues(args, venue);
			if (venue != null) {
				return lollaDb.update(venuesTable, args, venuesID + "=" + venue.getId(), null) > 0;
			}
		} else {
			return false;
		}
		return false;
	}

	private void populateContentValues(ContentValues initialValues, LollaObject object) {
		if (object.getClass() == Event.class) {
			Event event = (Event) object;
			initialValues.put(eventCategory, LollaDbUtil.escapeSQLString(event.getCategory()));
			initialValues.put(eventConference_category, LollaDbUtil.escapeSQLString(event.getConferenceCategory()));
			initialValues.put(eventApiLink, LollaDbUtil.escapeSQLString(event.getApiLink()));
			initialValues.put(eventDate, LollaDbUtil.escapeSQLString(event.getDate()));
			initialValues.put(eventEndTime, LollaDbUtil.escapeSQLString(event.getEndTime()));
			initialValues.put(eventGoogleDate, LollaDbUtil.escapeSQLString(event.getGoogleDate()));
			initialValues.put(eventID, event.getId());
			initialValues.put(eventName, LollaDbUtil.escapeSQLString(event.getTitle()));
			initialValues.put(eventPhotoThumb, LollaDbUtil.escapeSQLString(event.getPhotoThumb()));
			initialValues.put(eventPhotoThumbSmall, LollaDbUtil.escapeSQLString(event.getPhotoThumbSmall()));
			initialValues.put(eventStartTime, LollaDbUtil.escapeSQLString(event.getStartTime()));
			initialValues.put(eventTime, LollaDbUtil.escapeSQLString(event.getTime()));
			initialValues.put(eventUpdatedAt, LollaDbUtil.escapeSQLString(event.getUpdatedAt()));
			initialValues.put(eventVotes, event.getVotes());
			initialValues.put(venuesID, event.getVenueId());
		} else if (object.getClass() == Band.class) {
			Band band = (Band) object;
			initialValues.put(bandAltDescription, LollaDbUtil.escapeSQLString(band.getAltDescription()));
			initialValues.put(bandDescription, LollaDbUtil.escapeSQLString(band.getDescription()));
			initialValues.put(bandDisplayDate, LollaDbUtil.escapeSQLString(band.getDisplayDate()));
			initialValues.put(bandEchonestID, LollaDbUtil.escapeSQLString(band.getEchonestId()));
			initialValues.put(bandFanCount, band.getFanCount());
			initialValues.put(bandFlickrTag, LollaDbUtil.escapeSQLString(band.getFlickrTag()));
			initialValues.put(bandHomepage, LollaDbUtil.escapeSQLString(band.getHomepage()));
			initialValues.put(bandHometown, LollaDbUtil.escapeSQLString(band.getHometown()));
			initialValues.put(bandID, band.getId());
			initialValues.put(bandItunesLink, LollaDbUtil.escapeSQLString(band.getItunesLink()));
			initialValues.put(bandMainLineupPosition, band.getMainLineupPosition());
			initialValues.put(bandMusicbrainzID, LollaDbUtil.escapeSQLString(band.getMusicbrainzId()));
			initialValues.put(bandMyspaceLink, LollaDbUtil.escapeSQLString(band.getMyspaceLink()));
			initialValues.put(bandName, LollaDbUtil.escapeSQLString(band.getTitle()));
			initialValues.put(bandPhoto, LollaDbUtil.escapeSQLString(band.getPhoto()));
			initialValues.put(bandPhotoThumb, LollaDbUtil.escapeSQLString(band.getPhotoThumb()));
			initialValues.put(bandSetlist, LollaDbUtil.escapeSQLString(band.getSetlist()));
			initialValues.put(bandTwitterUsername, LollaDbUtil.escapeSQLString(band.getTwitterUsername()));
			initialValues.put(bandUpdatedAt, LollaDbUtil.escapeSQLString(band.getUpdatedAt()));
			initialValues.put(bandYoutubeID, LollaDbUtil.escapeSQLString(band.getYoutubeId()));
		} else if (object.getClass() == Venue.class) {
			Venue venue = (Venue) object;
			initialValues.put(venuesDescription, LollaDbUtil.escapeSQLString(venue.getDescription()));
			initialValues.put(venuesID, venue.getId());
			initialValues.put(venuesName, LollaDbUtil.escapeSQLString(venue.getTitle()));
			initialValues.put(venuesSortOrder, venue.getSortOrder());
			initialValues.put(venuesUpdatedAt, LollaDbUtil.escapeSQLString(venue.getUpdatedAt()));
		}
	}

	public LollaObject getByName(String name, Class<? extends LollaObject> type) {
		if (type.equals(Event.class)) {
			return getEventByName(name);
		} else if (type.equals(Band.class)) {
			return getBandByName(name);
		} else if (type.equals(Venue.class)) {
			return getVenueByName(name);
		} else {
			return null;
		}

	}

	public LollaObject getById(long id, Class<? extends LollaObject> type) {
		if (type.equals(Event.class)) {
			return getEventById(id);
		} else if (type.equals(Band.class)) {
			return getBandById(id);
		} else if (type.equals(Venue.class)) {
			return getVenueById(id);
		} else {
			return null;
		}
	}

	public List<? extends LollaObject> getAllByType(Class<? extends LollaObject> type) {
		if (type.equals(Band.class))
			return getAllBands();
		else if (type.equals(Event.class))
			return getAllEvents();
		else if (type.equals(Venue.class))
			return getAllVenues();
		else
			return null;
	}

	public long countType(Class<? extends LollaObject> type) {
		if (type.equals(Band.class)) {
			return countForTable(bandTable);
		} else if (type.equals(Event.class)) {
			return countForTable(eventTable);
		} else if (type.equals(Venue.class)) {
			return countForTable(venuesTable);
		} else
			return 0;
	}

	public List<Band> getAllBandsFromMySchedule() {
		List<Band> ret = new ArrayList<Band>();
		Cursor cursor = getCursorForTableName(myBandsTable);
		while (cursor.moveToNext()) {
			Band band;
			band = (Band) setValuesForTypeAndReturn(cursor, Band.class);
			ret.add(band);
		}
		cursor.close();
		return ret;
	}

	private long countForTable(String tableName) {
		Log.i("SQL", "select count(*) from " + tableName);
		return DatabaseUtils.queryNumEntries(lollaDb, tableName);
	}

	private List<Band> getAllBands() {
		List<Band> ret = new ArrayList<Band>();
		Cursor cursor = getCursorForTableName(bandTable);
		while (cursor.moveToNext()) {
			Band band;
			band = (Band) setValuesForTypeAndReturn(cursor, Band.class);
			ret.add(band);
		}
		cursor.close();
		return ret;
	}

	private List<Event> getAllEvents() {
		List<Event> ret = new ArrayList<Event>();
		Cursor cursor = getCursorForTableName(eventTable);
		while (cursor.moveToNext()) {
			Event event;
			event = buildShallowEvent(cursor);
			ret.add(event);
		}
		cursor.close();
		return ret;
	}

	private List<Venue> getAllVenues() {
		List<Venue> ret = new ArrayList<Venue>();
		Cursor cursor = getCursorForTableName(venuesTable);
		while (cursor.moveToNext()) {
			Venue venue;
			venue = (Venue) setValuesForTypeAndReturn(cursor, Venue.class);
			ret.add(venue);
		}
		cursor.close();
		return ret;
	}

	private Cursor getCursorForTableName(String tableName) {
		String query = "select * from " + tableName;
		Log.i("SQL", query);
		return lollaDb.rawQuery(query, null);
	}

	private Band getBandByName(String name) {
		String query = "select " + bandID + " from " + bandTable + " where " + bandName + " = '" + LollaDbUtil.escapeSQLString(name) + "'";
		Log.i("SQL", query);
		Cursor cursor = lollaDb.rawQuery(query, null);
		cursor.moveToFirst();
		Band ret = getBandById(cursor.getLong(0));
		cursor.close();
		return ret;
	}

	private Venue getVenueByName(String name) {
		String query = "select " + venuesID + " from " + venuesTable + " where " + venuesName + " = '" + LollaDbUtil.escapeSQLString(name) + "'";
		Log.i("SQL", query);
		Cursor cursor = lollaDb.rawQuery(query, null);
		cursor.moveToFirst();
		Venue ret = getVenueById(cursor.getLong(0));
		cursor.close();
		return ret;
	}

	private Event getEventByName(String name) {
		String query = "select " + eventID + " from " + eventTable + " where " + eventName + " = '" + LollaDbUtil.escapeSQLString(name) + "'";
		Log.i("SQL", query);
		Cursor cursor = lollaDb.rawQuery(query, null);
		cursor.moveToFirst();
		Event ret = getEventById(cursor.getLong(0));
		cursor.close();
		return ret;
	}
	
	public List<Event> getEventsByVenue(Venue venue) {
		List<Event> ret = new ArrayList<Event>();
		Log.i("SQL", "select " + eventID + " from " + eventTable + " where " + venuesID + " = " + venue.getId());
		Cursor cursor = lollaDb.query(eventTable, new String[]{eventID}, venuesID + " = " + venue.getId(),
				null, null, null, null);
		List<Long> eventIDs = new ArrayList<Long>();
		while (cursor.moveToNext()) {
			eventIDs.add(cursor.getLong(0));
		}
		cursor.close();
		Event event;
		for (long id : eventIDs) {
			String qry = "select * from " + eventTable + " where " + eventID + " = " + id;
			Log.i("SQL", qry);
			cursor = lollaDb.rawQuery(qry, null);
			cursor.moveToFirst();
			event = (Event) setValuesForTypeAndReturn(cursor, Event.class);
			cursor.close();
			ret.add(event);
		}
		return ret;
	}

	private Band getBandById(long id) {
		Band ret;
		String query = "select * from " + bandTable + " where " + bandID + " = " + id;
		Log.i("SQL", query);
		Cursor cursor = lollaDb.rawQuery(query, null);
		cursor.moveToFirst();
		ret = (Band) setValuesForTypeAndReturn(cursor, Band.class);
		cursor.close();
		return ret;
	}

	private Venue getVenueById(long id) {
		Venue ret;
		String query = "select * from " + venuesTable + " where " + venuesID + " = " + id;
		Log.i("SQL", query);
		Cursor cursor = lollaDb.rawQuery(query, null);
		cursor.moveToFirst();
		ret = (Venue) setValuesForTypeAndReturn(cursor, Venue.class);
		cursor.close();
		return ret;
	}

	private Event getEventById(long id) {
		Event ret;
		String query = "select * from " + eventTable + " where " + eventID + " = " + id;
		Log.i("SQL", query);
		Cursor cursor = lollaDb.rawQuery(query, null);
		cursor.moveToFirst();
		ret = (Event) setValuesForTypeAndReturn(cursor, Event.class);
		cursor.close();
		return ret;
	}


	public List<Event> getEventsByBand(Band band) {
		List<Event> ret = new ArrayList<Event>();
		Log.i("SQL", "select " + eventID + " from " + event_band_table + " where " + bandID + " = " + band.getId());
		Cursor cursor = lollaDb.query(event_band_table, new String[]{eventID}, bandID + " = " + band.getId(),
				null, null, null, null);
		List<Long> eventIDs = new ArrayList<Long>();
		while (cursor.moveToNext()) {
			eventIDs.add(cursor.getLong(0));
		}
		cursor.close();
		Event event;
		for (long id : eventIDs) {
			String query = "select * from " + eventTable + " where " + eventID + " = " + id;
			Log.i("SQL", query);
			cursor = lollaDb.rawQuery(query, null);
			cursor.moveToFirst();
			event = (Event) setValuesForTypeAndReturn(cursor, Event.class);
			cursor.close();
			ret.add(event);
		}
		return ret;
	}

	private LollaObject setValuesForTypeAndReturn(Cursor cursor, Class<? extends LollaObject> type) {
		if (type.equals(Band.class)) {
			Band band = new Band();
			byte[] altDescrip = cursor.getBlob(cursor.getColumnIndex(bandAltDescription));
			if (altDescrip != null)
				band.setAltDescription(new String(altDescrip));
			byte[] descrip = cursor.getBlob(cursor.getColumnIndex(bandDescription));
			if (descrip != null)
				band.setDescription(new String(descrip));
			band.setDisplayDate(cursor.getString(cursor.getColumnIndex(bandDisplayDate)));
			band.setEchonestId(cursor.getString(cursor.getColumnIndex(bandEchonestID)));
			band.setFanCount(cursor.getLong(cursor.getColumnIndex(bandFanCount)));
			band.setFlickrTag(cursor.getString(cursor.getColumnIndex(bandFlickrTag)));
			band.setHomepage(cursor.getString(cursor.getColumnIndex(bandHomepage)));
			band.setHometown(cursor.getString(cursor.getColumnIndex(bandHometown)));
			band.setId(cursor.getLong(cursor.getColumnIndex(bandID)));
			band.setItunesLink(cursor.getString(cursor.getColumnIndex(bandItunesLink)));
			band.setLink(cursor.getString(cursor.getColumnIndex(bandAPILink)));
			band.setMainLineupPosition(cursor.getLong(cursor.getColumnIndex(bandMainLineupPosition)));
			band.setMusicbrainzId(cursor.getString(cursor.getColumnIndex(bandMusicbrainzID)));
			band.setMyspaceLink(cursor.getString(cursor.getColumnIndex(bandMyspaceLink)));
			band.setPhoto(cursor.getString(cursor.getColumnIndex(bandPhoto)));
			band.setPhotoThumb(cursor.getString(cursor.getColumnIndex(bandPhotoThumb)));
			band.setSetlist(cursor.getString(cursor.getColumnIndex(bandSetlist)));
			band.setTitle(cursor.getString(cursor.getColumnIndex(bandName)));
			band.setTwitterUsername(cursor.getString(cursor.getColumnIndex(bandTwitterUsername)));
			band.setUpdatedAt(cursor.getString(cursor.getColumnIndex(bandUpdatedAt)));
			band.setUpdatedAt(cursor.getString(cursor.getColumnIndex(bandUpdatedAt)));
			band.setYoutubeId(cursor.getString(cursor.getColumnIndex(bandYoutubeID)));
			return band;
		} else if (type.equals(Event.class)) {
			Event event = new Event();
			event.setCategory(cursor.getString(cursor.getColumnIndex(eventCategory)));
			event.setApiLink(cursor.getString(cursor.getColumnIndex(eventApiLink)));
			event.setConferenceCategory(cursor.getString(cursor.getColumnIndex(eventConference_category)));
			event.setDate(cursor.getString(cursor.getColumnIndex(eventDate)));
			event.setEndTime(cursor.getString(cursor.getColumnIndex(eventEndTime)));
			event.setGoogleDate(cursor.getString(cursor.getColumnIndex(eventGoogleDate)));
			event.setPhotoThumb(cursor.getString(cursor.getColumnIndex(eventPhotoThumb)));
			event.setPhotoThumbSmall(cursor.getString(cursor.getColumnIndex(eventPhotoThumbSmall)));
			event.setStartTime(cursor.getString(cursor.getColumnIndex(eventStartTime)));
			event.setTime(cursor.getString(cursor.getColumnIndex(eventTime)));
			event.setUpdatedAt(cursor.getString(cursor.getColumnIndex(eventUpdatedAt)));
			event.setVotes(cursor.getLong(cursor.getColumnIndex(eventVotes)));
			event.setVenueId(cursor.getLong(cursor.getColumnIndex(venuesID)));
			event.setId(cursor.getLong(cursor.getColumnIndex(eventID)));
			event.setTitle(cursor.getString(cursor.getColumnIndex(eventName)));
			event.setLink(cursor.getString(cursor.getColumnIndex(eventApiLink)));
			event.setBandIds(getBandIdsForEventId(event.getId()));
			return event;
		} else if (type.equals(Venue.class)) {
			Venue venue = new Venue();
			byte[] descrip = cursor.getBlob(cursor.getColumnIndex(venuesDescription));
			if (descrip != null)
				venue.setDescription(new String(descrip));
			venue.setId(cursor.getLong(cursor.getColumnIndex(venuesID)));
			venue.setSortOrder(cursor.getLong(cursor.getColumnIndex(venuesSortOrder)));
			venue.setTitle(cursor.getString(cursor.getColumnIndex(venuesName)));
			venue.setUpdatedAt(cursor.getString(cursor.getColumnIndex(venuesUpdatedAt)));
			return venue;
		} else {
			return null;
		}
	}
	
	/**
	 * Builds and returns an Event object from a managed Cursor,
	 * but it does not retrieve relationship data.
	 */
	private Event buildShallowEvent(Cursor cursor) {
		Event event = new Event();
		event.setCategory(cursor.getString(cursor.getColumnIndex(eventCategory)));
		event.setApiLink(cursor.getString(cursor.getColumnIndex(eventApiLink)));
		event.setConferenceCategory(cursor.getString(cursor.getColumnIndex(eventConference_category)));
		event.setDate(cursor.getString(cursor.getColumnIndex(eventDate)));
		event.setEndTime(cursor.getString(cursor.getColumnIndex(eventEndTime)));
		event.setGoogleDate(cursor.getString(cursor.getColumnIndex(eventGoogleDate)));
		event.setPhotoThumb(cursor.getString(cursor.getColumnIndex(eventPhotoThumb)));
		event.setPhotoThumbSmall(cursor.getString(cursor.getColumnIndex(eventPhotoThumbSmall)));
		event.setStartTime(cursor.getString(cursor.getColumnIndex(eventStartTime)));
		event.setTime(cursor.getString(cursor.getColumnIndex(eventTime)));
		event.setUpdatedAt(cursor.getString(cursor.getColumnIndex(eventUpdatedAt)));
		event.setVotes(cursor.getLong(cursor.getColumnIndex(eventVotes)));
		event.setVenueId(cursor.getLong(cursor.getColumnIndex(venuesID)));
		event.setId(cursor.getLong(cursor.getColumnIndex(eventID)));
		event.setTitle(cursor.getString(cursor.getColumnIndex(eventName)));
		event.setLink(cursor.getString(cursor.getColumnIndex(eventApiLink)));
		return event;
	}

	private List<Long> getBandIdsForEventId(long eventId) {
		String query = "select " + bandID + " from " + event_band_table + " where " + eventID + " = " + eventId;
		Log.i("SQL", query);
		Cursor bandIdCursor = lollaDb.rawQuery(query, null);
		List<Long> theBandIds = new ArrayList<Long>();
		while (!bandIdCursor.isLast()) {
			bandIdCursor.moveToNext();
			theBandIds.add(bandIdCursor.getLong(0));
		}
		bandIdCursor.close();
		return theBandIds;
	}

	public List<Band> getBandsByEvent(Event event) {
		List<Long> bandIds = event.getBandIds();
		List<Band> ret = new ArrayList<Band>();
		for (Long id : bandIds)
			ret.add(getBandById(id));
		return ret;
	}

	public Venue getVenueByEvent(Event event) {
		return getVenueById(event.getVenueId());
	}
	
	public void buildAndSaveImages() {
		String query = "select " + bandID + ", " + bandPhoto + ", " + bandPhotoThumb + " from " + bandTable;
		Log.i("SQL", query);
		Cursor cursor = lollaDb.rawQuery(query, null);
		List<Pair<Long, Pair<String, String>>> imageData = new ArrayList<Pair<Long, Pair<String, String>>>();
		while (cursor.moveToNext()) {
			long bandId = cursor.getLong(0);
			byte[] imgUrlBlob = cursor.getBlob(1);
			byte[] imgThumbUrlBlob = cursor.getBlob(2);
			if (imgUrlBlob != null && imgThumbUrlBlob != null) {
				String imgUrl = new String(imgUrlBlob);
				imgUrl = imgUrl.substring(0, imgUrl.length() - 1);
				String imgThumbUrl = new String(imgThumbUrlBlob);
				imgThumbUrl = imgThumbUrl.substring(0, imgThumbUrl.length() - 1);
				imageData.add(new Pair<Long, Pair<String, String>>(bandId, new Pair<String, String>(imgUrl, imgThumbUrl)));
			}
		}
		cursor.close();
		saveImageData(imageData);
	}
	
	private void saveImageData(List<Pair<Long, Pair<String, String>>> imageData) {
		Log.i(TAG, "Persisting " + imageData.size() + " image data rows");
		for (Pair<Long, Pair<String, String>> data : imageData) {
			try {
				saveImageFromUrl(data);
			} catch (MalformedURLException e) {
				Log.e("LollaService", "Unable to save image - MalformedURLException occurred");
			} catch (IOException e) {
				Log.e("LollaService", "Unable to save image from - IOException occurred");
			}
		}
		Log.i(TAG, "Image data persisted successfully");
	}
	
	/**
	private void saveImageFromUrl(Pair<Long, Pair<String, String>> data) throws ClientProtocolException, IOException {
		DefaultHttpClient mHttpClient = new DefaultHttpClient();
		HttpGet mHttpGet = new HttpGet(data.second.first);
		HttpResponse mHttpResponse = mHttpClient.execute(mHttpGet);
		HttpGet mHttpGetThumb = new HttpGet(data.second.second);
		HttpResponse mHttpResponseThumb = mHttpClient.execute(mHttpGetThumb);
		if (mHttpResponse.getStatusLine().getStatusCode() == HttpStatus.SC_OK && mHttpResponseThumb.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {
		  HttpEntity entity = mHttpResponse.getEntity();
		  HttpEntity entityThumb = mHttpResponseThumb.getEntity();
		    if (entity != null && entityThumb != null) {
		      // insert to database
		      ContentValues values = new ContentValues();
		      values.put(bandID, data.first);
		      values.put(image, EntityUtils.toByteArray(entity));
		      values.put(imageThumb, EntityUtils.toByteArray(entityThumb));
		      lollaDb.insert(bandImagesTable, null, values);
		    }
		}
	}
	*/
	
	private void saveImageFromUrl(Pair<Long, Pair<String, String>> data) throws MalformedURLException, IOException {
		Bitmap imgBitmap = BitmapFactory.decodeStream((InputStream) new URL(data.second.first).getContent());
		Bitmap imgThumbBitmap = BitmapFactory.decodeStream((InputStream) new URL(data.second.second).getContent());
		ByteArrayOutputStream bos = new ByteArrayOutputStream();
		imgBitmap.compress(CompressFormat.PNG, 0, bos);
		byte[] imgBytes = bos.toByteArray();
		bos = new ByteArrayOutputStream();
		imgThumbBitmap.compress(CompressFormat.PNG, 0, bos);
		byte[] imgThumbBytes = bos.toByteArray();
	    if (imgBytes != null && imgThumbBytes != null) {
	        // insert to database
		    ContentValues values = new ContentValues();
		    values.put(bandID, data.first);
		    values.put(image, imgBytes);
		    values.put(imageThumb, imgThumbBytes);
		    lollaDb.insert(bandImagesTable, null, values);
		}
	}

}
